<?php

use yii\db\Migration;

/**
 * 创建贷款相关表
 */
class m240325_000001_create_loan_tables extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
        }

        // 检查并删除已存在的表
        if ($this->db->schema->getTableSchema('{{%tbl_loan_comparison}}') !== null) {
            $this->dropTable('{{%tbl_loan_comparison}}');
        }
        if ($this->db->schema->getTableSchema('{{%tbl_principal_result}}') !== null) {
            $this->dropTable('{{%tbl_principal_result}}');
        }
        if ($this->db->schema->getTableSchema('{{%tbl_equal_result}}') !== null) {
            $this->dropTable('{{%tbl_equal_result}}');
        }
        if ($this->db->schema->getTableSchema('{{%tbl_fund_loan_detail}}') !== null) {
            $this->dropTable('{{%tbl_fund_loan_detail}}');
        }
        if ($this->db->schema->getTableSchema('{{%tbl_commercial_loan_detail}}') !== null) {
            $this->dropTable('{{%tbl_commercial_loan_detail}}');
        }
        if ($this->db->schema->getTableSchema('{{%tbl_loan_record}}') !== null) {
            $this->dropTable('{{%tbl_loan_record}}');
        }

        // 创建贷款记录表
        $this->createTable('{{%tbl_loan_record}}', [
            'id' => $this->primaryKey(),
            'user_id' => $this->integer()->notNull()->comment('用户ID'),
            'loan_type' => $this->string(20)->notNull()->comment('贷款类型：commercial-商业贷款，fund-公积金贷款'),
            'repayment_type' => $this->string(20)->notNull()->comment('还款方式：equal-等额本息，principal-等额本金'),
            'years' => $this->integer()->notNull()->comment('贷款年限'),
            'total_amount' => $this->decimal(10, 2)->notNull()->comment('贷款总额'),
            'created_at' => $this->dateTime()->notNull()->comment('创建时间'),
            'updated_at' => $this->dateTime()->defaultExpression('CURRENT_TIMESTAMP')->append('ON UPDATE CURRENT_TIMESTAMP')->comment('更新时间'),
        ], $tableOptions);

        // 添加索引
        $this->createIndex('idx-loan_record-user_id', '{{%tbl_loan_record}}', 'user_id');

        // 创建商业贷款详情表
        $this->createTable('{{%tbl_commercial_loan_detail}}', [
            'id' => $this->primaryKey(),
            'record_id' => $this->integer()->notNull()->comment('关联的贷款记录ID'),
            'amount' => $this->decimal(10, 2)->notNull()->comment('商业贷款金额(万元)'),
            'rate' => $this->decimal(5, 2)->notNull()->comment('商业贷款利率(%)'),
            'basis_points' => $this->integer()->null()->comment('基点调整'),
            'adjusted_rate' => $this->decimal(5, 2)->notNull()->comment('调整后的利率(%)'),
        ], $tableOptions);

        // 创建公积金贷款详情表
        $this->createTable('{{%tbl_fund_loan_detail}}', [
            'id' => $this->primaryKey(),
            'record_id' => $this->integer()->notNull()->comment('关联的贷款记录ID'),
            'amount' => $this->decimal(10, 2)->notNull()->comment('公积金贷款金额(万元)'),
            'rate' => $this->decimal(5, 2)->notNull()->comment('公积金贷款利率(%)'),
        ], $tableOptions);

        // 创建等额本息结果表
        $this->createTable('{{%tbl_equal_result}}', [
            'id' => $this->primaryKey(),
            'record_id' => $this->integer()->notNull()->comment('关联的贷款记录ID'),
            'total_loan' => $this->decimal(12, 2)->notNull()->comment('贷款总额(元)'),
            'total_repayment' => $this->decimal(12, 2)->notNull()->comment('还款总额(元)'),
            'total_interest' => $this->decimal(12, 2)->notNull()->comment('总利息(元)'),
            'monthly_payment' => $this->decimal(10, 2)->notNull()->comment('月供(元)'),
        ], $tableOptions);

        // 创建等额本金结果表
        $this->createTable('{{%tbl_principal_result}}', [
            'id' => $this->primaryKey(),
            'record_id' => $this->integer()->notNull()->comment('关联的贷款记录ID'),
            'total_loan' => $this->decimal(12, 2)->notNull()->comment('贷款总额(元)'),
            'total_repayment' => $this->decimal(12, 2)->notNull()->comment('还款总额(元)'),
            'total_interest' => $this->decimal(12, 2)->notNull()->comment('总利息(元)'),
            'first_month_payment' => $this->decimal(10, 2)->notNull()->comment('首月还款(元)'),
            'decrease_per_month' => $this->decimal(10, 2)->notNull()->comment('每月递减(元)'),
            'last_month_payment' => $this->decimal(10, 2)->notNull()->comment('末月还款(元)'),
        ], $tableOptions);

        // 创建贷款对比表
        $this->createTable('{{%tbl_loan_comparison}}', [
            'id' => $this->primaryKey(),
            'user_id' => $this->integer()->notNull()->comment('用户ID'),
            'record1_id' => $this->integer()->notNull()->comment('对比的第一条记录ID'),
            'record2_id' => $this->integer()->notNull()->comment('对比的第二条记录ID'),
            'conclusion' => $this->text()->null()->comment('对比结论'),
            'created_at' => $this->dateTime()->notNull()->comment('创建时间'),
            'updated_at' => $this->dateTime()->defaultExpression('CURRENT_TIMESTAMP')->append('ON UPDATE CURRENT_TIMESTAMP')->comment('更新时间'),
        ], $tableOptions);

        // 添加外键约束
        // $this->addForeignKey(
        //     'fk-loan_record-user_id',
        //     '{{%tbl_loan_record}}',
        //     'user_id',
        //     '{{%user}}',
        //     'id',
        //     'CASCADE'
        // );

        $this->addForeignKey(
            'fk-commercial_loan_detail-record_id',
            '{{%tbl_commercial_loan_detail}}',
            'record_id',
            '{{%tbl_loan_record}}',
            'id',
            'CASCADE'
        );

        $this->addForeignKey(
            'fk-fund_loan_detail-record_id',
            '{{%tbl_fund_loan_detail}}',
            'record_id',
            '{{%tbl_loan_record}}',
            'id',
            'CASCADE'
        );

        $this->addForeignKey(
            'fk-equal_result-record_id',
            '{{%tbl_equal_result}}',
            'record_id',
            '{{%tbl_loan_record}}',
            'id',
            'CASCADE'
        );

        $this->addForeignKey(
            'fk-principal_result-record_id',
            '{{%tbl_principal_result}}',
            'record_id',
            '{{%tbl_loan_record}}',
            'id',
            'CASCADE'
        );

        // $this->addForeignKey(
        //     'fk-loan_comparison-user_id',
        //     '{{%tbl_loan_comparison}}',
        //     'user_id',
        //     '{{%user}}',
        //     'id',
        //     'CASCADE'
        // );

        $this->addForeignKey(
            'fk-loan_comparison-record1_id',
            '{{%tbl_loan_comparison}}',
            'record1_id',
            '{{%tbl_loan_record}}',
            'id',
            'CASCADE'
        );

        $this->addForeignKey(
            'fk-loan_comparison-record2_id',
            '{{%tbl_loan_comparison}}',
            'record2_id',
            '{{%tbl_loan_record}}',
            'id',
            'CASCADE'
        );

        // 添加唯一索引
        $this->createIndex(
            'idx-commercial_loan_detail-record_id',
            '{{%tbl_commercial_loan_detail}}',
            'record_id',
            true
        );

        $this->createIndex(
            'idx-fund_loan_detail-record_id',
            '{{%tbl_fund_loan_detail}}',
            'record_id',
            true
        );

        $this->createIndex(
            'idx-equal_result-record_id',
            '{{%tbl_equal_result}}',
            'record_id',
            true
        );

        $this->createIndex(
            'idx-principal_result-record_id',
            '{{%tbl_principal_result}}',
            'record_id',
            true
        );
    }

    /**
     * {@inheritdoc}
     */
    public function down()
    {
        // 删除外键约束
        $this->dropForeignKey('fk-loan_comparison-record2_id', '{{%tbl_loan_comparison}}');
        $this->dropForeignKey('fk-loan_comparison-record1_id', '{{%tbl_loan_comparison}}');
        $this->dropForeignKey('fk-principal_result-record_id', '{{%tbl_principal_result}}');
        $this->dropForeignKey('fk-equal_result-record_id', '{{%tbl_equal_result}}');
        $this->dropForeignKey('fk-fund_loan_detail-record_id', '{{%tbl_fund_loan_detail}}');
        $this->dropForeignKey('fk-commercial_loan_detail-record_id', '{{%tbl_commercial_loan_detail}}');
        // $this->dropForeignKey('fk-loan_record-user_id', '{{%tbl_loan_record}}');

        // 删除表
        $this->dropTable('{{%tbl_loan_comparison}}');
        $this->dropTable('{{%tbl_principal_result}}');
        $this->dropTable('{{%tbl_equal_result}}');
        $this->dropTable('{{%tbl_fund_loan_detail}}');
        $this->dropTable('{{%tbl_commercial_loan_detail}}');
        $this->dropTable('{{%tbl_loan_record}}');
    }
} 